package com.saikuo.service.dao.impl;

import com.saikuo.service.constant.SortType;
import com.saikuo.service.dao.IMedicineDao;
import com.saikuo.service.vo.MedicineDetailView;
import com.saikuo.service.vo.MedicinePicView;
import com.saikuo.service.vo.MedicineView;
import com.saikuo.service.vo.SearchTagView;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

/**
 * Created by shanchangyu on 16/10/17.
 */
@Repository
public class MedicineDaoImpl extends JdbcDaoSupport implements IMedicineDao {
    private static final BeanPropertyRowMapper<MedicineView> mapper = new
            BeanPropertyRowMapper<>(MedicineView.class);
    private static final BeanPropertyRowMapper<MedicineDetailView> mapperDetail = new
            BeanPropertyRowMapper<>(MedicineDetailView.class);
    private static final BeanPropertyRowMapper<MedicinePicView> mapperPic = new
            BeanPropertyRowMapper<>(MedicinePicView.class);
    private static final BeanPropertyRowMapper<SearchTagView> mapperTag = new
            BeanPropertyRowMapper<>(SearchTagView.class);
    @Autowired
    MedicineDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    private static final String GET_HOT_SEARCH_TAG = "select * from y_search_tag where status=0 ";

    @Override
    public List<SearchTagView> getMedicineTopicSearchTag() {
        List<SearchTagView> result = this.getJdbcTemplate().query(GET_HOT_SEARCH_TAG, mapperTag);
        if (CollectionUtils.isEmpty(result)) {
            return Collections.emptyList();
        }
        return result;
    }


    private static final String GET_MEDICINES_BY_CATEGORY_ID = "select m.id,m.num as medicineNum,m.name,m.head_pic,m.introduction,m.specification,m.price,m.sale_count from y_category_medicine c"
            + " left join y_medicine m on c.medicine_id=m.id where c.category_id=?";


    @Override
    public List<MedicineView> getMedicinesByCategoryId(int categoryId, int sortType, int from, int limit) {
        String sortParam = " order by m.price limit ?,?";
        if (sortType == SortType.SALES.getValue()) {
            sortParam = " order by m.sale_count desc limit ?,?";
        }
        List<MedicineView> result = this.getJdbcTemplate().query(GET_MEDICINES_BY_CATEGORY_ID + sortParam,
                new Object[]{categoryId, from, limit}, mapper);
        if (CollectionUtils.isEmpty(result)) {
            return Collections.emptyList();
        }
        return result;
    }

    private static final String GET_HOT_MEDICINES = "select id,name,num as medicineNum,head_pic ,introduction,specification,price,sale_count from " +
            "y_medicine where 1=1  ";

    @Override
    public List<MedicineView> getHotMedicines(int from, int limit) {
        List<MedicineView> result = this.getJdbcTemplate().query(GET_HOT_MEDICINES + " order by sale_count desc limit ?,?",
                new Object[]{from, limit}, mapper);
        if (CollectionUtils.isEmpty(result)) {
            return Collections.emptyList();
        }
        return result;
    }


    private static final String GET_MEDICINES_BY_ID = "select id,num as medicineNum,name,introduction,price,specification,direction,sale_count,producer, head_pic ,status" +
            " from y_medicine  where id=?";

    @Override
    public MedicineDetailView getMedicineDetailById(int medicineId) {
        List<MedicineDetailView> result = this.getJdbcTemplate().query(GET_MEDICINES_BY_ID, new Object[]{medicineId}, mapperDetail);
        if (CollectionUtils.isNotEmpty(result)) {
            return result.get(0);
        }
        return null;
    }

    private static final String GET_MEDICINES_BY_ORDER_ID = "select m.name,m.price,m.head_pic ,m.specification,m.introduction,d.id,d.medicine_num,d.count as saleCount from y_order o " +
            " left join  y_order_detail d on o.order_id=d.order_id " +
            " left join y_medicine m on d.medicine_num=m.num where o.order_id=?";

    @Override
    public List<MedicineView> getMedicineDetailByOrderId(Long orderId) {
        List<MedicineView> result = this.getJdbcTemplate().query(GET_MEDICINES_BY_ORDER_ID, new Object[]{orderId}, mapper);
        if (CollectionUtils.isNotEmpty(result)) {
            return result;
        }
        return Collections.emptyList();
    }

    private static final String GET_MEDICINE_PIC_BY_MEDICINE_ID = "select id,medicine_id,picture_url from " +
            "y_medicine_pic where medicine_id=?";

    @Override
    public List<MedicinePicView> getMedicinePicsByMedicineId(int medicineId) {
        List<MedicinePicView> result = this.getJdbcTemplate().query(GET_MEDICINE_PIC_BY_MEDICINE_ID, new Object[]{medicineId}, mapperPic);
        if (CollectionUtils.isEmpty(result)) {
            return Collections.emptyList();
        }
        return result;
    }

    @Override
    public String getTopicSaleMedicine(int topicSaleId) {
        return this.getJdbcTemplate().queryForObject("select medicine_ids from y_topic_sale where id=?", new Object[]{topicSaleId}, String.class);
    }

    private final String GET_TOPIC_SALE_MEDICINES = "select id,name,num as medicineNum,head_pic,introduction,specification,price,sale_count from " +
            "y_medicine where id in (:medicineIds) order by sale_count desc ";

    @Override
    public List<MedicineView> getMedicinesByIds(List<Integer> medicineIds) {
        if (!medicineIds.isEmpty()) {
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
                    this.getJdbcTemplate());
            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("medicineIds", medicineIds);
            List<MedicineView> result = namedParameterJdbcTemplate.query(
                    GET_TOPIC_SALE_MEDICINES, parameters, mapper);
            if (CollectionUtils.isEmpty(result)) {
                return Collections.emptyList();
            }
            return result;
        }
        return Collections.emptyList();
    }

    private static final String UPDATE_MEDICINE_SALE_COUNT = "update y_medicine set sale_count=sale_count+? where num=?";

    @Override
    public int updateMedicineSaleCount(final List<MedicineView> list) {
        int[] count = this.getJdbcTemplate()
                .batchUpdate(
                        UPDATE_MEDICINE_SALE_COUNT,
                        new BatchPreparedStatementSetter() {
                            public void setValues(PreparedStatement ps, int i)
                                    throws SQLException {
                                MedicineView orderDetail = list.get(i);
                                ps.setInt(1, orderDetail.getSaleCount());
                                ps.setString(2, orderDetail.getMedicineNum());
                            }

                            @Override
                            public int getBatchSize() {
                                return list.size();
                            }
                        });
        return count.length;
    }
}
